package com.kh.tool.exceldatachecker;

import com.kh.tool.exceldatachecker.exception.ExcelParseException;
import com.kh.tool.exceldatachecker.exception.ExcelRowTooLongException;
import lombok.AllArgsConstructor;
import lombok.Data;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.util.*;
import java.util.stream.Collectors;

public class ExcelDataParser {

    /***
     属性map的字段解释：
     map: {
         key : 每一列的表头名称
         value: {
             key:单元格里的具体值
             value: [
                {
                 cell:1,
                 row:2,
                 value:"个体名称",
                 id:每行数据的主键ID //解析excel时不赋值
                 column:"" //表头对应的字段名
                }
            ]
         }
     }
     * Map<表头名称,Map<每个单元格里的值,该值出现过的所有坐标>>
     */
    private Map<String,Map<String,List<Value>>> map = new HashMap<>();
    private List<ExcelColumns> columnsList;
    private List<Map<String,Object>> excelDataList;

    private ExcelDataParser.BusinessChecker dbChecker;

    private Map<String,List<Value>> listMap =  new HashMap<>();

    private ExcelDataParser(List<ExcelColumns> columns, TableHeaderChecker headerChecker, MultipartFile file, int maxRow) {
        this.columnsList = columns;
        try {
            this.excelDataList = parseExcel(file,headerChecker, columnsList);
            if (!CollectionUtils.isEmpty(excelDataList) && excelDataList.size() > maxRow) {
                throw new ExcelRowTooLongException();
            }
            buildMap(excelDataList);
        } catch (IOException e) {
            throw new ExcelParseException("IO错误");
        }
    }

    public ExcelDataParser(TableHeaderChecker headerChecker, MultipartFile file,int maxRow) {
        this(headerChecker.createTableHeader(),headerChecker,file,maxRow);
    }

    public void addDbChecker(BusinessChecker checker) {
        this.dbChecker = checker;
    }

    public ExcelDataParser(List<ExcelColumns> columns, MultipartFile file) {
        this(columns,null,file,5000);
    }

    @AllArgsConstructor
    @Data
    public static class Value {
        /**
         * 该值在第几列
         */
        private int cell;
        /**
         * 改值在第几行
         */
        private int raw;
        /**
         * 改值所处列的表头
         */
        private String value;
        /**
         * 改值所处列表头对应的字段名
         */
        private String column;

        /**
         * 改值所处行的主键ID 解析excel表格时不处理这个字段
         */
        private Long id;

        /**
         * 该值的错误原因
         */
        private String errorMsg;

        public Value(String value, String column, Long id) {
            this.value = value;
            this.column = column;
            this.id = id;
        }

        public boolean isUpdate() {
            return id != null;
        }

        @Override
        public boolean equals(Object o) {
            if (this == o) return true;
            if (o == null || getClass() != o.getClass()) return false;
            Value value1 = (Value) o;
            return cell == value1.cell &&
                    raw == value1.raw &&
                    Objects.equals(value, value1.value) &&
                    Objects.equals(column, value1.column);
        }

        @Override
        public int hashCode() {
            return Objects.hash(cell, raw, value, column);
        }
    }

    public  List<Map<String,Object>> parseExcel(MultipartFile file, TableHeaderChecker tableHeaderChecker, List<ExcelColumns> columns ) throws IOException {
        List<Map<String,Object>> mapListAll = new ArrayList<>();
        String fileName = file.getOriginalFilename();//文件名
        if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
            throw new ExcelParseException("文件格式不正确");
        }
        boolean isExcel2003 = true;
        if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
            isExcel2003 = false;
        }
        InputStream is = file.getInputStream();
        org.apache.poi.ss.usermodel.Workbook wb = null;
        if (isExcel2003) {
            wb = new HSSFWorkbook(is);
        } else {
            wb = new XSSFWorkbook(is);
        }
        Sheet sheet = wb.getSheetAt(0);
        if(sheet!=null){
            if (tableHeaderChecker != null) {
                if (!tableHeaderChecker.check(sheet)) {
                    throw new ExcelParseException("导入的模板不符合!请重新下载模板！");
                }
            }
            Row row;
            for (int r = 1; r <= sheet.getLastRowNum(); r++) {
                row = sheet.getRow(r);
                if (row == null){
                    continue;
                }
                int cellTypeString = Cell.CELL_TYPE_STRING;//默认字符串类型
                Map<String,Object> tmp = new HashMap<>();
                boolean rowIsEmpty=true;//该行是否整行都是空
                for (int c=0;c<columns.size();c++) {
                    String cellVal = getCellVal(row.getCell(c), cellTypeString);
                    if (!StringUtils.isEmpty(cellVal) && !StringUtils.isEmpty(cellVal.trim())) {
                        rowIsEmpty = false;
                    }
                    tmp.put(columns.get(c).getKey(),cellVal);
                }
                if (rowIsEmpty){
                    continue;
                } else {
                    mapListAll.add(tmp);
                }
            }
        }
        return mapListAll;
    }

    /**
     * 传入列对象获取列值
     * @param cell
     * @param cellType
     * @return
     */
    public static String getCellVal(Cell cell,int cellType) {
        if(null == cell){
            return null;
        }
        cell.setCellType(cellType);
        return  cell.getStringCellValue().trim();
    }

    public List<Value> getErrorInfoBatch() {
        List<Value> errorValue = new ArrayList<>();
        for (int i = 0; i < columnsList.size(); i++) {
            ExcelColumns excelColumns = columnsList.get(i);
            Map<String, List<Value>> stringListMap = map.get(excelColumns.getKey());

            for (Map.Entry<String,List<Value>> entry : stringListMap.entrySet()) {
                List<Value> v = entry.getValue();

                for (int j = 0; j < v.size(); j++) {
                    /***
                     * 同列重复判断
                     */
                    if (excelColumns.isRepeatCheck()) {
                        if (v.size() > 1) {
                            errorValue.addAll(v);
                            final String rowCollect = v.stream().map(r -> r.getRaw() + "").collect(Collectors.joining(","));
                            v.get(0).setErrorMsg(excelColumns.getValue() + ":" + entry.getKey() + "和(" + rowCollect + ")行重复");
                            break;
                        }
                    }

                    /***
                     * 单元格内容格式判断
                     */
                    List<BusinessChecker> formatCheckers = excelColumns.getFormatCheckers();
                    boolean valueIsError = false;
                    for (BusinessChecker c : formatCheckers) {
                        if (!c.check(v.get(j))) {
                            errorValue.add(v.get(j));
                            valueIsError = true;
                            break;
                        }
                    }

                    if (valueIsError) {
                        continue;
                    }

                    final String headerCellTitle = excelColumns.getKey();
                    List<Value> values = listMap.computeIfAbsent(headerCellTitle, l -> new ArrayList<>());
                    values.add(v.get(j));
                    if (valueIsError) {
                        continue;
                    }
                }
            }
        }

        if (dbChecker != null) {
            for (Map.Entry<String,List<Value>> entry : listMap.entrySet()) {
                final String key = entry.getKey();
                final List<Value> values = entry.getValue();
                dbChecker.check(key,values);

                errorValue.addAll(values.stream().filter(o->!StringUtils.isEmpty(o.getErrorMsg())).collect(Collectors.toList()));
            }
        }

        System.out.println(listMap);
        return errorValue;
    }

    public List<Value> getErrorInfo() {
        List<Value> errorValue = new ArrayList<>();
        for (int i = 0; i < columnsList.size(); i++) {
            ExcelColumns excelColumns = columnsList.get(i);
            Map<String, List<Value>> stringListMap = map.get(excelColumns.getKey());

            for (Map.Entry<String,List<Value>> entry : stringListMap.entrySet()) {
                List<Value> v = entry.getValue();

                for (int j = 0; j < v.size(); j++) {
                    /***
                     * 同列重复判断
                     */
                    if (excelColumns.isRepeatCheck()) {
                        if (v.size() > 1) {
                            errorValue.addAll(v);
                            final String rowCollect = v.stream().map(r -> r.getRaw() + "").collect(Collectors.joining(","));
                            v.get(0).setErrorMsg(excelColumns.getValue() + ":" + entry.getKey() + "和(" + rowCollect + ")行重复");
                            break;
                        }
                    }

                    /***
                     * 单元格内容格式判断
                     */
                    List<BusinessChecker> formatCheckers = excelColumns.getFormatCheckers();
                    boolean valueIsError = false;
                    for (BusinessChecker c : formatCheckers) {
                        if (!c.check(v.get(j))) {
                            errorValue.add(v.get(j));
                            valueIsError = true;
                            break;
                        }
                    }

                    if (valueIsError) {
                        continue;
                    }

                    /***
                     * 数据库字段校验
                     */
                    List<BusinessChecker> dbCheckers = excelColumns.getDbCheckers();
                    for (BusinessChecker c : dbCheckers) {
                        if (!c.check(v.get(j))) {
                            errorValue.add(v.get(j));
                            valueIsError = true;
                            break;
                        }
                    }

                    if (valueIsError) {
                        continue;
                    }
                }
            }
        }
        return errorValue;
    }


    private void buildMap(List<Map<String,Object>> dataList) {
        final List<String> colStrList = columnsList.stream().map(o -> o.getKey()).collect(Collectors.toList());
        for (int row = 0; row < dataList.size(); row++) {
            Map<String, Object> entity = dataList.get(row);
            for (int cell = 0; cell < colStrList.size(); cell++) {
                String col = colStrList.get(cell);
                Map<String, List<Value>> kv = map.computeIfAbsent(col, k -> new HashMap<>());

                String cellValue = entity.get(col) == null ? "-" : entity.get(col).toString();
                List<Value> values = kv.computeIfAbsent(cellValue, v -> new ArrayList<>());
                values.add(new Value(cell,row+1,cellValue,col,null,""));
                kv.put(cellValue,values);
            }
        }
    }

    public static boolean find(int row, int cell, List<Value> errorValues) {
        boolean b = errorValues.stream().anyMatch(value -> value.getCell() == cell && value.getRaw() == row);
        return b;
    }

    public static Optional<Value> findErrorValue(int row, int cell, List<Value> errorValues) {
        Optional<Value> first = errorValues.stream().filter(value -> value.getCell() == cell && value.getRaw() == row).findFirst();
        return first;
    }

    public List<Map<String, Object>> getExcelDataList() {
        return excelDataList;
    }

    public interface BusinessChecker {
        boolean check(Value toBeVerified);
        default void check(String key, List<Value> values) {}
    }
}
